package com.yjzx.util.common.util.excel.alibaba;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.yjzx.util.common.util.ExceptionUtil;
import com.yjzx.util.common.util.StopWatchLog;
import com.yjzx.util.common.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StopWatch;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author yjzx
 * @date 2023/5/5
 * @Description excel导入导出类
 */
@Slf4j
public class EasyExcelUtil {
    public static void throwException(Exception exception) {
        ExceptionUtil.throwToRunException(exception);
    }

    /**
     * 读取Excel文件返回数据集合，不包含表头，默认读取第一个sheet数据
     *
     * @param inputStream   输入流
     * @param tClass        数据映射类
     * @param modelName     模块名
     * @param excelListener 监听类
     * @return List 结果集
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class<T> tClass, String modelName, ExcelListener<T> excelListener) {
        return readExcel(inputStream, 0, tClass, modelName, excelListener);
    }

    /**
     * 读取Excel文件返回数据集合，不包含表头，默认读取第一个sheet数据
     *
     * @param inputStream 输入流
     * @param tClass      数据映射类
     * @param modelName   模块名
     * @return List 结果集
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class<T> tClass, String modelName) {
        return readExcel(inputStream, tClass, modelName, new ExcelListener<>(tClass));
    }

    /**
     * 读取Excel文件返回数据集合，不包含表头，默认读取第一个sheet数据
     *
     * @param inputStream 输入流
     * @param sheetNo     sheet名
     * @param tClass      数据映射类
     * @param modelName   模块名
     * @return List 结果集
     */
    public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> tClass, String modelName) {
        return readExcel(inputStream, sheetNo, tClass, modelName, new ExcelListener<>(tClass));
    }

    /**
     * 读取Excel文件返回数据集合，不包含表头，默认读取第一个sheet数据
     *
     * @param inputStream   输入流
     * @param sheetNo       sheet名
     * @param tClass        数据映射类
     * @param modelName     模块名
     * @param excelListener 监听类
     * @return List 结果集
     */
    public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> tClass, String modelName, ExcelListener<T> excelListener) {
        if (Objects.isNull(inputStream) || Objects.isNull(tClass)) {
            return null;
        }
        List<T> datas = null;
        StopWatchLog watchLog = null;
        try {
            watchLog = new StopWatchLog(new StopWatch(), false).start("读取excel数据");
            ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
            if (!Objects.isNull(sheetNo)) {
                read.sheet(sheetNo).doRead();
            } else {
                ExcelReader excelReader = read.build();
                excelReader.readAll();
                excelReader.finish();
            }
            datas = excelListener.getDataList();
            watchLog.endRule("{}导入excel数据：{}条，导入耗时：{}秒！", modelName, datas.size(), watchLog.stopWatch.getTotalTimeSeconds());
        } catch (Exception e) {
            if (watchLog != null) {
                watchLog.errorRule(modelName + "导出异常，数据{}" + datas, e);
            }
            throwException(e);
        }
        return datas;
    }

    public static List<Map<Integer, Object>> readExcelToMap(InputStream inputStream, Integer sheetNo, String modelName, ExcelListener<Map<Integer, Object>> excelListener) {
        if (Objects.isNull(inputStream)) {
            return null;
        }
        List<Map<Integer, Object>> datas = null;
        StopWatchLog watchLog = null;
        try {
            watchLog = new StopWatchLog(new StopWatch(), false).start("读取excel数据");
            ExcelReaderBuilder read = EasyExcel.read(inputStream, excelListener);
            if (!Objects.isNull(sheetNo)) {
                read.sheet(sheetNo).doRead();
            } else {
                ExcelReader excelReader = read.build();
                excelReader.readAll();
                excelReader.finish();
            }
            datas = excelListener.getDataList();
            watchLog.endRule("{}导入excel数据：{}条，导入耗时：{}秒！", modelName, datas.size(), watchLog.stopWatch.getTotalTimeSeconds());
        } catch (Exception e) {
            if (watchLog != null) {
                watchLog.errorRule(modelName + "导出异常，数据{}" + datas, e);
            }
            throwException(e);
        }
        return datas;
    }

    public static List<List<Object>> readExcelToList(InputStream inputStream, Integer sheetNo, String modelName, ExcelListener<Map<Integer, Object>> excelListener) {
        final List<Map<Integer, Object>> maps = readExcelToMap(inputStream, sheetNo, modelName, excelListener);
        return maps != null ? maps.stream().map(x -> {
            int integer = x.keySet().stream().max(Comparator.comparingInt(Integer::intValue)).orElse(0);
            ArrayList<Object> list = new ArrayList<>(integer);
            for (int i = 0; i < integer; i++) {
                list.add(x.get(i));
            }
            return list;
        }).collect(Collectors.toList()) : Collections.emptyList();
    }

    /**
     * 不带模板输出数据到Excel，数据传输类属性用 @ExcelProperty("") 标注
     *
     * @param outputStream 输出流
     * @param data         数据集合
     * @param sheetName    sheet名
     * @param clazz        输出格式
     * @return：
     */
    public static <T> void writeExcel(OutputStream outputStream, String sheetName, List<T> data, Class<T> clazz, String modelName) {
        writeExcel(true, outputStream, new ColumnHandler(), sheetName, data, clazz, modelName);
    }


    /**
     * 不带模板输出数据到Excel，数据传输类属性用 @ExcelProperty("") 类中自带自适应
     *
     * @param response  响应对象
     * @param tClass    输出格式
     * @param datas     输出的数据
     * @param fileName  输出的文件名
     * @param modelName 输出的模块名
     * @return：
     */
    public static <T> void writeExcel(HttpServletResponse response, String sheetName, Class<T> tClass, List<T> datas, String fileName, String modelName) {
        StopWatchLog watchLog = null;
        if (Objects.isNull(response) || Objects.isNull(tClass)) {
            return;
        }
        try {
            watchLog = new StopWatchLog(new StopWatch(), false).start("导出excel数据");
            fileName = getExcelName(fileName);
            response.setStatus(HttpServletResponse.SC_OK);
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
            writeExcel(false, response.getOutputStream(), new ColumnHandler(), sheetName, datas, tClass, modelName);
            watchLog.endRule("{}导出excel数据：{}条，导出耗时：{}秒！", modelName, datas.size(), watchLog.stopWatch.getTotalTimeSeconds());
        } catch (Exception e) {
            if (watchLog != null) {
                watchLog.errorRule(modelName + "导出异常，数据{}" + datas, e);
            }
            throwException(e);
        }
    }

    /**
     * 不带模板输出数据到Excel
     *
     * @param isLog        是否打印日志
     * @param outputStream 输出流
     * @param writeHandler excel配置类
     * @param sheetName    sheet名
     * @param data         数据
     * @param clazz        输出类型class
     * @param modelName    模块名
     * @param <T>
     */
    public static <T> void writeExcel(boolean isLog, OutputStream outputStream, WriteHandler writeHandler, String sheetName, List<T> data, Class<T> clazz, String modelName) {
        StopWatchLog watchLog = null;
        if (isLog) {
            watchLog = new StopWatchLog(new StopWatch(), false).start("导出excel数据");
        }
        ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz)
                .registerWriteHandler(writeHandler)
                .excelType(ExcelTypeEnum.XLSX
                ).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
                .registerWriteHandler(writeHandler).build();

        writeExcel(false, excelWriter, writeSheet, data, modelName);
        excelWriter.finish();
        if (isLog) {
            watchLog.endRule("{}导出excel数据：{}条，导出耗时：{}秒！", modelName, data.size(), watchLog.stopWatch.getTotalTimeSeconds());
        }
    }

    /**
     * 不带模板输出数据到Excel（可在一个写入器多次写入）写入结束后使用finish
     *
     * @param isLog        是否打印日志
     * @param excelWriter  写入器
     * @param writeSheet   写入sheet
     * @param data         数据
     * @param modelName    模块名
     * @param <T>
     */
    public static <T> void writeExcel(boolean isLog, ExcelWriter excelWriter, WriteSheet writeSheet, List<T> data, String modelName) {
        StopWatchLog watchLog = null;
        try {
            if (isLog) {
                watchLog = new StopWatchLog(new StopWatch(), false).start("导出excel数据");
            }
            excelWriter.write(data, writeSheet);
            if (isLog) {
                watchLog.endRule("{}导出excel数据：{}条，导出耗时：{}秒！", modelName, data.size(), watchLog.stopWatch.getTotalTimeSeconds());
            }
        } catch (Exception e) {
            if (watchLog != null) {
                watchLog.errorRule(modelName + "导出异常，数据{}" + data, e);
            }
            throwException(e);
        }
    }


    /**
     * 不带模板输出数据到Excel，数据传输类属性用 @ExcelProperty("") 标注，动态的行头
     *

     */
    /**
     * @param response     响应对象
     * @param writeHandler excel配置类
     * @param fileName     文件名
     * @param sheetName    sheet名
     * @param head         动态头
     * @param data         动态数据
     * @param modelName    模块名
     */
    public static void dynamicWriteExcel(HttpServletResponse response, WriteHandler writeHandler, String fileName, String sheetName,
                                         List<?> data, List<List<String>> head, String modelName) {
        StopWatchLog watchLog = null;
        try {
            watchLog = new StopWatchLog(new StopWatch(), false).start("导出excel数据");
            fileName = getExcelName(fileName);
            response.setContentType("application/vnd.ms-excel; charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new ColumnHandler())
                    .head(head)
                    .sheet(sheetName)
                    .doWrite(data);
            watchLog.endRule("{}导出excel数据：{}条，导出耗时：{}秒！", modelName, data.size(), watchLog.stopWatch.getTotalTimeSeconds());
        } catch (Exception e) {
            if (watchLog != null) {
                watchLog.errorRule(modelName + "导出异常，数据{}" + data, e);
            }
            throwException(e);
        }
    }

    /**
     * 带模板输出数据到Excel，数据传输类属性用 @ExcelProperty("") 标注
     *
     * @param inputStream  输入流，模板文件
     * @param outputStream 输出流
     * @param tClass       输出格式
     * @param datas        输出的数据
     * @return：
     */
    public static <T> void writeExcel(InputStream inputStream, OutputStream outputStream, Class<T> tClass, List<T> datas, String modelName) {
        StopWatchLog watchLog = null;
        if (Objects.isNull(outputStream) || Objects.isNull(tClass)) {
            return;
        }
        try {
            watchLog = new StopWatchLog(new StopWatch(), false).start("导出excel数据");
            EasyExcel.write(outputStream, tClass)
                    .excelType(ExcelTypeEnum.XLSX)
                    .withTemplate(inputStream)
                    .sheet("sheet").doFill(datas);
            watchLog.endRule("{}导出excel数据：{}条，导出耗时：{}秒！", modelName, datas.size(), watchLog.stopWatch.getTotalTimeSeconds());
        } catch (Exception e) {
            if (watchLog != null) {
                watchLog.errorRule(modelName + "导出异常，数据{}" + datas, e);
            }
            throwException(e);
        }
    }

    private static String getExcelName(String fileName) {
        String[] str = {".xls", ".xlsx"};
        if (StringUtils.isBlank(fileName)) {
            fileName = "excel.xls";
        } else {
            int i = fileName.lastIndexOf(".");
            if (i != -1) {
                String file = fileName.substring(i);
                for (String s : str) {
                    if (s.equalsIgnoreCase(file)) {
                        return fileName;
                    }
                }
            }
            fileName = fileName + str[0];
        }
        return fileName;
    }

    /**
     * 获取列名
     *
     * @param columnIdx 列(从1开始)
     * @return
     */
    public static String getRowName(int columnIdx) {
        if (columnIdx < 1){
            return "";
        }
        StringBuilder builder = new StringBuilder();
        while (columnIdx > 0) {
            columnIdx--; // 因为Excel列是从A(1)开始的，而我们的计数是从0开始的
            int remainder = columnIdx % 26;
            char letter = (char) ('A' + remainder);
            builder.insert(0, letter);
            columnIdx /= 26;
        }
        return builder.toString();
    }


}
